import pymysql
import pandas as pd
import uuid
from Database import DatabaseHandler

db_handler = DatabaseHandler(host='localhost', port=3306, user='root', password='123456')
db_handler.create_database('test')
db_handler.cursor.execute('USE test')

# 创建表
sql = ("CREATE TABLE IF NOT EXISTS School("
       "id VARCHAR(255)  PRIMARY KEY, "  # 假设您需要一个自增ID  
       "name VARCHAR(225), "
       "local VARCHAR(225), "
       "level VARCHAR(225), "
       "type VARCHAR(225), "
       "feature VARCHAR(225), "
       "universityStatus VARCHAR(225))"
       )
db_handler.cursor.execute(sql)
print('表创建或已存在，sql执行成功')

# #
# # # 增加数据
# #
df = pd.read_excel('C:/Users/姚宇康/Desktop/日常文件/学习/合并后的院校总表.xlsx')  # 假设你想从默认工作表读取
df.fillna('', inplace=True)  # 填充空值

df['id'] = ['S-' + str(uuid.uuid4())[:8] for _ in range(len(df))]


# 将 DataFrame 转换为列表的元组（因为 executemany 需要这种格式）
data_to_insert = df.to_dict(orient='records')
# 插入数据
sql_insert = 'INSERT INTO  School VALUES ( %s,%s, %s, %s, %s, %s, %s)'
try:
    db_handler.cursor.executemany(sql_insert, df.to_records(index=False).tolist())
    # cursor.commit()
    print(db_handler.cursor.rowcount, "记录插入成功。")
except pymysql.MySQLError as e:
    print(f"数据库错误：{e}")
    # 注意：由于autocommit=True，通常不需要手动回滚

db_handler.close_connection()
